package com.example.framework.util;

import java.io.File;

import com.example.framework.base.CustomApplication;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Environment;
import android.util.Log;

public class SqliteDBHelper extends SQLiteOpenHelper
{
	public static final String DB_NAME = "xiaqianqian.db";
	public static final int VERSION = 1;
	public static final int INSERT_ERROR = -1;

	public SQLiteDatabase dbReadable;
	private static String path = "";

	public SqliteDBHelper(Context context)
	{
		super(context, DB_NAME, null, VERSION);

		dbReadable = this.getReadableDatabase();

		Log.i(CustomApplication.SQLITE_TAG, "sqliteDB----->created.");

		path = dbReadable.getPath().substring(0, dbReadable.getPath().length() - DB_NAME.length());
	}

	@Override
	public void onCreate(SQLiteDatabase db)
	{
		db.execSQL("create table test(id INTEGER PRIMARY KEY AUTOINCREMENT,name varchar(50));");
		Log.i(CustomApplication.SQLITE_TAG, "SqliteOpenHelper.onCreate: create a default table successfully!");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
	{
		db.execSQL("DROP TABLE IF EXISTS " + "test");
		Log.i(CustomApplication.SQLITE_TAG, "SqliteOpenHelper.onUpgrade: version value changed!");
		onCreate(db);
	}

	/**
	 * Create Table on created SqliteDatabase.
	 * 
	 * @param db
	 *            the created SqliteDatabase.
	 * @param table
	 *            name of Database preparing to create.
	 * @param fields
	 *            of preparing to add.
	 * @param types
	 *            correlation with fields.
	 */
	public void createTable(String table, String[] fields, String[] types)
	{
		String population = "";

		if (fields.length == types.length && fields.length != 0)
		{
			for (int i = 0; i < types.length; i++)
			{
				population += fields[i] + " " + types[i] + ",";
			}

			population = population.substring(0, population.length() - 1);
			
			try
			{
				dbReadable.execSQL("create table " + table + "(" + population + ");");
			}
			catch (Exception e)
			{
				Log.e(CustomApplication.SQLITE_TAG, "error on that sqlite execute operation createting table: the SQL string is invalid!");
			}

			Log.i(CustomApplication.SQLITE_TAG, "sqlite table create successfully!");
		}
		else
		{
			Log.i(CustomApplication.SQLITE_TAG, "sqlite table create failed!");
		}

	}

	/**
	 * Convenience method for inserting a row into the database.
	 * 
	 * @param table
	 *            the table to insert the row into
	 * @param values
	 *            this map contains the initial column values for the row. The
	 *            keys should be the column names and the values the column
	 *            values
	 * 
	 * @return the row ID of the newly inserted row, or -1 if an error occurred
	 */
	public long insert(String table, ContentValues values)
	{
		if (null == dbReadable)
		{
			dbReadable = this.getReadableDatabase();
		}

		long rowId = dbReadable.insert(table, null, values);

		Log.i(CustomApplication.SQLITE_TAG, "sqlite execute insert operation, rowId is " + rowId);

		return rowId;
	}

	/**
	 * Convenience method for deleting rows in the database.
	 * 
	 * @param table
	 *            the table to delete from
	 * @param whereClause
	 *            the optional WHERE clause to apply when deleting. Passing null
	 *            will delete all rows.
	 * @param whereArgs
	 *            if whereClause was populated like {"name=" + name}, the
	 *            whereArgs should be null; on the other hand, You may include
	 *            ?s in whereClause, which will be replaced by the values from
	 *            whereArgs. The values will be bound as Strings.
	 * 
	 * @return the number of rows affected if a whereClause is passed in, 0
	 *         otherwise. To remove all rows and get a count pass "1" as the
	 *         whereClause.
	 */
	public int delete(String table, String whereClause, String[] whereArgs)
	{
		if (null == dbReadable)
		{
			dbReadable = this.getReadableDatabase();
		}
		if (null == whereClause)
		{
			return deleteAll(table);
		}

		int affectedrows = dbReadable.delete(table, whereClause, whereArgs);

		Log.i(CustomApplication.SQLITE_TAG, "sqlite execute delete operation, number of rows affected is " + affectedrows);

		return affectedrows;
	}

	private int deleteAll(String table)
	{
		if (null == dbReadable)
		{
			dbReadable = this.getReadableDatabase();
		}

		int delcount = dbReadable.delete(table, null, null);

		if (0 != delcount)
		{
			ContentValues content = new ContentValues();
			content.put("seq", 0);
			dbReadable.update("sqlite_sequence ", content, "name=?", new String[] { table });
		}

		Log.i(CustomApplication.SQLITE_TAG, "sqlite execute delete operation, number of rows affected is " + delcount);

		return delcount;
	}

	/**
	 * Convenience method for updating rows in the database.
	 * 
	 * @param table
	 *            the table to update in
	 * @param values
	 *            a map from column names to new column values. null is a valid
	 *            value that will be translated to NULL.
	 * @param whereClause
	 *            the optional WHERE clause to apply when updating. Passing null
	 *            will update all rows.
	 * @param whereArgs
	 *            if whereClause was populated like {"name=" + name}, the
	 *            whereArgs should be null; on the other hand, You may include
	 *            ?s in whereClause, which will be replaced by the values from
	 *            whereArgs. The values will be bound as Strings.
	 * 
	 * @return the number of rows affected
	 */
	public int update(String table, ContentValues values, String whereClause, String[] whereArgs)
	{
		if (null == dbReadable)
		{
			dbReadable = this.getReadableDatabase();
		}

		int affectedrows = dbReadable.update(table, values, whereClause, whereArgs);

		Log.i(CustomApplication.SQLITE_TAG, "sqlite execute update operation, number of rows affected is " + affectedrows);

		return affectedrows;
	}

	/**
	 * Query the given table, returning a {@link Cursor} over the result set.
	 * 
	 * @param table
	 *            The table name to compile the query against.
	 * @param columns
	 *            A list of which columns to return. Passing null will return
	 *            all columns, which is discouraged to prevent reading data from
	 *            storage that isn't going to be used.
	 * @param selection
	 *            A filter declaring which rows to return, formatted as an SQL
	 *            WHERE clause (excluding the WHERE itself). Passing null will
	 *            return all rows for the given table.
	 * @param selectionArgs
	 *            You may include ?s in selection, which will be replaced by the
	 *            values from selectionArgs, in order that they appear in the
	 *            selection. The values will be bound as Strings.
	 * @return A {@link Cursor} object, which is positioned before the first
	 *         entry. Note that {@link Cursor}s are not synchronized, see the
	 *         documentation for more details.
	 * @see Cursor
	 */
	public Cursor query(String table, String[] columns, String selection, String[] selectionArgs)
	{
		if (null == dbReadable)
		{
			dbReadable = this.getReadableDatabase();
		}

		Log.i(CustomApplication.SQLITE_TAG, "sqlite execute query operation");

		return dbReadable.query(table, columns, selection, selectionArgs, null, null, null);
	}

	/**
	 * Query the given table, returning a {@link Cursor} over the result set.
	 * 
	 * @param table
	 *            The table name to compile the query against.
	 * @param columns
	 *            A list of which columns to return. Passing null will return
	 *            all columns, which is discouraged to prevent reading data from
	 *            storage that isn't going to be used.
	 * @param selection
	 *            A filter declaring which rows to return, formatted as an SQL
	 *            WHERE clause (excluding the WHERE itself). Passing null will
	 *            return all rows for the given table.
	 * @param selectionArgs
	 *            You may include ?s in selection, which will be replaced by the
	 *            values from selectionArgs, in order that they appear in the
	 *            selection. The values will be bound as Strings.
	 * @param groupBy
	 *            A filter declaring how to group rows, formatted as an SQL
	 *            GROUP BY clause (excluding the GROUP BY itself). Passing null
	 *            will cause the rows to not be grouped.
	 * @param having
	 *            A filter declare which row groups to include in the cursor, if
	 *            row grouping is being used, formatted as an SQL HAVING clause
	 *            (excluding the HAVING itself). Passing null will cause all row
	 *            groups to be included, and is required when row grouping is
	 *            not being used.
	 * @param orderBy
	 *            How to order the rows, formatted as an SQL ORDER BY clause
	 *            (excluding the ORDER BY itself). Passing null will use the
	 *            default sort order, which may be unordered.
	 * @return A {@link Cursor} object, which is positioned before the first
	 *         entry. Note that {@link Cursor}s are not synchronized, see the
	 *         documentation for more details.
	 * @see Cursor
	 */
	public Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String orderBy)
	{
		if (null == dbReadable)
		{
			dbReadable = this.getReadableDatabase();
		}

		Log.i(CustomApplication.SQLITE_TAG, "sqlite execute query operation");

		return dbReadable.query(table, columns, selection, selectionArgs, groupBy, null, orderBy);
	}

	/**
	 * Query the given URL, returning a {@link Cursor} over the result set.
	 * 
	 * @param distinct
	 *            true if you want each row to be unique, false otherwise.
	 * @param table
	 *            The table name to compile the query against.
	 * @param columns
	 *            A list of which columns to return. Passing null will return
	 *            all columns, which is discouraged to prevent reading data from
	 *            storage that isn't going to be used.
	 * @param selection
	 *            A filter declaring which rows to return, formatted as an SQL
	 *            WHERE clause (excluding the WHERE itself). Passing null will
	 *            return all rows for the given table.
	 * @param selectionArgs
	 *            You may include ?s in selection, which will be replaced by the
	 *            values from selectionArgs, in order that they appear in the
	 *            selection. The values will be bound as Strings.
	 * @param groupBy
	 *            A filter declaring how to group rows, formatted as an SQL
	 *            GROUP BY clause (excluding the GROUP BY itself). Passing null
	 *            will cause the rows to not be grouped.
	 * @param having
	 *            A filter declare which row groups to include in the cursor, if
	 *            row grouping is being used, formatted as an SQL HAVING clause
	 *            (excluding the HAVING itself). Passing null will cause all row
	 *            groups to be included, and is required when row grouping is
	 *            not being used.
	 * @param orderBy
	 *            How to order the rows, formatted as an SQL ORDER BY clause
	 *            (excluding the ORDER BY itself). Passing null will use the
	 *            default sort order, which may be unordered.
	 * @param limit
	 *            Limits the number of rows returned by the query, formatted as
	 *            LIMIT clause. Passing null denotes no LIMIT clause.
	 * @return A {@link Cursor} object, which is positioned before the first
	 *         entry. Note that {@link Cursor}s are not synchronized, see the
	 *         documentation for more details.
	 * @see Cursor
	 */
	public Cursor query(boolean distinct, String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit)
	{
		if (null == dbReadable)
		{
			dbReadable = this.getReadableDatabase();
		}

		Log.i(CustomApplication.SQLITE_TAG, "sqlite execute query operation");

		return dbReadable.query(distinct, table, columns, selection, selectionArgs, groupBy, null, orderBy, limit);
	}

	public void closeDatabase()
	{
		if (null != dbReadable)
		{
			dbReadable.close();
			Log.i(CustomApplication.SQLITE_TAG, "sqlite execute close database operation, but deprecate useing this method!");
		}
	}

	/**
	 * Deletes a database including its journal file and other auxiliary files
	 * that may have been created by the database engine.
	 * 
	 * @param path
	 *            The database file path.
	 * @return True if the database was successfully deleted.
	 */
	public boolean deleteDatabase()
	{
		if (null == dbReadable)
		{
			dbReadable = this.getReadableDatabase();
		}

		Log.i(CustomApplication.SQLITE_TAG, "sqlite execute delete the default/only database operation");

		return SQLiteDatabase.deleteDatabase(new File(dbReadable.getPath()));
	}

	/**
	 * Get size of files in folder which SqliteDatabase resides.
	 * 
	 * @return size of files including SqliteDatabase file.
	 */
	public static float getCacheSize()
	{
		float dirSize = 0;
		if (isSDCardEnable())
		{
			File dirFile = new File(path);

			File[] files = dirFile.listFiles();

			for (File file : files)
			{
				dirSize += file.length();
			}
		}

		Log.i(CustomApplication.SQLITE_TAG, "get database size: " + dirSize + "B");

		return dirSize;
	}

	/**
	 * Delete all files in folder which SqliteDatabase resides.
	 */
	public static void clearCache()
	{

		if (isSDCardEnable())
		{
			File dir = new File(path);

			File[] files = dir.listFiles();

			if (files == null || files.length == 0)
			{
				return;
			}

			for (File file : files)
			{
				file.delete();
			}

			Log.d(CustomApplication.IMAGECACHE_TAG, "cleared all files related to database :" + path);
		}
	}

	public static boolean isSDCardEnable()
	{
		return Environment.getExternalStorageState().equals(Environment.MEDIA_MOUNTED);
	}

}
